package com.infore.qualityControl.repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.infore.qualityControl.po.SpanDrift;

@Repository
public interface ISpanDriftRepository extends JpaRepository<SpanDrift, String>, JpaSpecificationExecutor<SpanDrift> {

	/** 根据测试时间、时间倒序查询 **/
	List<SpanDrift> findByTimeBetweenOrderByTimeDesc(Date startDate, Date endDate);
	
	/** 根据测试时间与指标ID、监测点ID、时间倒序查询 **/
	List<SpanDrift> findByTimeBetweenAndIndexCodeInAndStationCodeInOrderByTimeDesc(Date startDate, Date endDate,
			List<String> indexCodes, List<String> stationCodes);
	
	/** 根据测试时间与指标ID、监测点ID、时间升序查询 **/
	List<SpanDrift> findByTimeBetweenAndIndexCodeInAndStationCodeInOrderByTimeAsc(Date startDate, Date endDate,
			List<String> indexCodes, List<String> stationCodes);

	/** 根据测试时间与指标ID、监测点ID、时间倒序分页查询 **/
	Page<SpanDrift> findByTimeBetweenAndIndexCodeInAndStationCodeInOrderByTimeDesc(Date startDate, Date endDate,
			List<String> indexCodes, List<String> stationCodes, Pageable pageable);
	
	/** 根据测试时间、监测点ID、时间倒序查询 **/
	List<SpanDrift> findByTimeBetweenAndStationCodeInOrderByTimeDesc(Date startDate, Date endDate,
			List<String> stationCodes);
	
	/** 根据时间、监测点ID、指标ID查询 **/
	SpanDrift findByTimeAndStationCodeAndIndexCode(Date date, String stationCode, String indexCode);
	
	/** 根据站点ID、监测项分组及时间条件获取实传数量与(跨度漂移的)合格数量 **/
	@Query(value = "SELECT AREA_NAME,BASIN_NAME,STATION_CODE,STATION_NAME,INDEX_CODE,COUNT(1) COUNT,"  
			+ "SUM(CASE LAST_QUALIFICATION WHEN '合格' THEN 1 ELSE 0 END) LAST_QUALIFICATION "  
			+ "FROM SPAN_DRIFT WHERE TIME BETWEEN :startDate AND :endDate AND STATION_CODE IN (:stationCodes) "
			+ "GROUP BY AREA_NAME,BASIN_NAME,STATION_CODE,STATION_NAME,INDEX_CODE ORDER BY STATION_CODE", nativeQuery = true)
	List<Map<String, Object>> getSpanDriftExamineData(@Param("startDate") Date startDate,
			@Param("endDate") Date endDate, @Param("stationCodes") List<String> stationCodes);
	
	/** 根据站点ID、监测项分组及时间条件获取实传数量与(跨度漂移与跨度核查的)合格数量 **/
	@Query(value = "SELECT AREA_NAME,BASIN_NAME,OPERATION_NAME,STATION_CODE,STATION_NAME,INDEX_CODE,COUNT(1) COUNT,"  
			+ "SUM(CASE WHEN QUALIFICATION = '合格' AND LAST_QUALIFICATION = '合格' THEN 1 ELSE 0 END) QUALIFICATION "  
			+ "FROM SPAN_DRIFT WHERE TIME BETWEEN :startDate AND :endDate AND STATION_CODE IN (:stationCodes) "
			+ "GROUP BY AREA_NAME,BASIN_NAME,OPERATION_NAME,STATION_CODE,STATION_NAME,INDEX_CODE ORDER BY STATION_CODE", nativeQuery = true)
	List<Map<String, Object>> getExamineData(@Param("startDate") Date startDate,
			@Param("endDate") Date endDate, @Param("stationCodes") List<String> stationCodes);
	
	/** 根据站点ID、监测项获取最新的跨度漂移等数据 **/
	@Query(value = "SELECT A.* FROM SPAN_DRIFT A,"  
			+ "(select STATION_CODE,INDEX_CODE,MAX(TIME) TIME from SPAN_DRIFT WHERE STATION_CODE = :stationCode AND INDEX_CODE = :indexCode "  
			+ "GROUP BY STATION_CODE,INDEX_CODE) B " 
			+ "WHERE A.STATION_CODE = B.STATION_CODE AND A.INDEX_CODE = B.INDEX_CODE AND A.TIME = B.TIME", nativeQuery = true)
	List<SpanDrift> getLatestData(@Param("stationCode") String stationCode, @Param("indexCode") String indexCode);
	
	/** 根据站点ID、时间范围、监测项、配置参数ID获取各配置参数的质控次数，不合格次数等信息 **/
	@Query(value = "select QUALITY_DISPOSE_ID,MIN(TO_CHAR(TIME,'yyyy-MM-dd HH24:mi:ss')) TIME," + 
			"SUM(CASE WHEN QUALIFICATION = '不合格' OR LAST_QUALIFICATION = '不合格' THEN 1 ELSE 0 END) UNQUALIFIEDNUMBER," + 
			"COUNT(QUALITY_DISPOSE_ID) QUALITYNUMBER " + 
			"FROM SPAN_DRIFT WHERE TIME BETWEEN :startDate AND :endDate " + 
			"AND STATION_CODE IN (:stationCodes) AND INDEX_CODE = :indexCode " + 
			"AND QUALITY_DISPOSE_ID IN (:qualityDisposeIds) " + 
			"GROUP BY QUALITY_DISPOSE_ID", nativeQuery = true)
	List<Map<String, Object>> getQualityDisposeData(@Param("startDate") Date startDate, @Param("endDate") Date endDate,
			@Param("stationCodes") List<String> stationCodes, @Param("indexCode") String indexCode,
			@Param("qualityDisposeIds") List<String> qualityDisposeIds);
	
	/** 根据站点ID、监测项分组及时间与条件获取质控总数、合格数量与不合格数量 **/
	@Query(value = "SELECT INDEX_CODE,COUNT(1) COUNT," + 
			"SUM(CASE WHEN QUALIFICATION = '合格' AND LAST_QUALIFICATION = '合格' THEN 1 ELSE 0 END) QUALIFIED," + 
			"SUM(CASE WHEN QUALIFICATION = '不合格' OR QUALIFICATION IS NULL OR LAST_QUALIFICATION = '不合格' OR LAST_QUALIFICATION IS NULL THEN 1 ELSE 0 END) UNQUALIFIED " + 
			"FROM SPAN_DRIFT WHERE TIME BETWEEN :startDate AND :endDate " + 
			"AND STATION_CODE IN (:stationCodes) AND INDEX_CODE IN (:indexCodes) GROUP BY INDEX_CODE", nativeQuery = true)
	List<Map<String, Object>> getQualifiedData(@Param("startDate") Date startDate,
			@Param("endDate") Date endDate, @Param("stationCodes") List<String> stationCodes, @Param("indexCodes") List<String> indexCodes);
}
